<?php
class Database {
    // 数据库连接
    private $pdo;
    private $mysqli;
    
    // 查询构建相关属性
    private $table;
    private $select = '*';
    private $where = [];
    private $joins = [];
    private $limit;
    private $offset;
    private $order = [];
    private $group = [];
    private $values = [];
    private $updateData = [];
    
    // 预处理语句参数
    private $params = [];
    
    // 构造函数，初始化数据库连接
    public function __construct($dbConfig, $usePdo = true) {
        if ($usePdo) {
            $this->connectPdo($dbConfig);
        } else {
            $this->connectMysqli($dbConfig);
        }
    }
    
    // 连接PDO
    private function connectPdo($dbConfig) {
        try {
            $dsn = "mysql:host={$dbConfig['host']};dbname={$dbConfig['dbname']};charset=utf8";
            $this->pdo = new PDO(
                $dsn,
                $dbConfig['username'],
                $dbConfig['password'],
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES => false,
                ]
            );
        } catch (PDOException $e) {
            die("PDO连接失败: " . $e->getMessage());
        }
    }
    
    // 连接MySQLi
    private function connectMysqli($dbConfig) {
        $this->mysqli = new mysqli(
            $dbConfig['host'],
            $dbConfig['username'],
            $dbConfig['password'],
            $dbConfig['dbname']
        );
        
        if ($this->mysqli->connect_error) {
            die("MySQLi连接失败: " . $this->mysqli->connect_error);
        }
        
        $this->mysqli->set_charset("utf8");
    }
    
    // 设置要操作的表
    public function table($table) {
        $this->table = $table;
        return $this;
    }
    
    // 设置SELECT子句
    public function select($columns = '*') {
        $this->select = is_array($columns) ? implode(', ', $columns) : $columns;
        return $this;
    }
    
    // 添加WHERE条件 - 使用预处理语句
    public function where($column, $operator = null, $value = null) {
        if (func_num_args() == 2) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->where[] = [
            'column' => $column,
            'operator' => $operator,
            'value' => $value,
            'type' => 'and'
        ];
        return $this;
    }
    
    // 添加OR WHERE条件 - 使用预处理语句
    public function orWhere($column, $operator = null, $value = null) {
        if (func_num_args() == 2) {
            $value = $operator;
            $operator = '=';
        }
        
        $this->where[] = [
            'column' => $column,
            'operator' => $operator,
            'value' => $value,
            'type' => 'or'
        ];
        return $this;
    }
    
    // 添加INNER JOIN
    public function join($table, $first, $operator, $second) {
        $this->joins[] = [
            'type' => 'INNER',
            'table' => $table,
            'first' => $first,
            'operator' => $operator,
            'second' => $second
        ];
        return $this;
    }
    
    // 添加LEFT JOIN
    public function leftJoin($table, $first, $operator, $second) {
        $this->joins[] = [
            'type' => 'LEFT',
            'table' => $table,
            'first' => $first,
            'operator' => $operator,
            'second' => $second
        ];
        return $this;
    }
    
    // 添加ORDER BY
    public function orderBy($column, $direction = 'ASC') {
        $this->order[] = "$column $direction";
        return $this;
    }
    
    // 添加GROUP BY
    public function groupBy($column) {
        $this->group[] = $column;
        return $this;
    }
    
    // 设置LIMIT
    public function limit($limit) {
        $this->limit = $limit;
        return $this;
    }
    
    // 设置OFFSET
    public function offset($offset) {
        $this->offset = $offset;
        return $this;
    }
    
    // 设置INSERT数据
    public function insert($data) {
        $this->values = $data;
        return $this;
    }
    
    // 设置UPDATE数据
    public function update($data) {
        $this->updateData = $data;
        return $this;
    }
    
    // 执行SELECT查询并返回所有结果
    public function get() {
        $sql = $this->buildSelectQuery();
        
        if ($this->pdo) {
            return $this->executePdoQuery($sql, $this->getWhereParams());
        } else {
            return $this->executeMysqliQuery($sql, $this->getWhereParams());
        }
    }
    
    // 执行SELECT查询并返回单行结果
    public function first() {
        $this->limit(1);
        $sql = $this->buildSelectQuery();
        
        if ($this->pdo) {
            $results = $this->executePdoQuery($sql, $this->getWhereParams());
            return count($results) > 0 ? $results[0] : null;
        } else {
            $results = $this->executeMysqliQuery($sql, $this->getWhereParams());
            return count($results) > 0 ? $results[0] : null;
        }
    }
    
    // 执行INSERT查询
    public function save() {
        if (empty($this->values) && empty($this->updateData)) {
            throw new Exception("没有数据可保存");
        }
        
        if (!empty($this->values)) {
            list($sql, $params) = $this->buildInsertQuery();
        } else {
            list($sql, $params) = $this->buildUpdateQuery();
        }
        
        if ($this->pdo) {
            return $this->executePdoStatement($sql, $params);
        } else {
            return $this->executeMysqliStatement($sql, $params);
        }
    }
    
    // 执行DELETE查询
    public function delete() {
        list($sql, $params) = $this->buildDeleteQuery();
        
        if ($this->pdo) {
            return $this->executePdoStatement($sql, $params);
        } else {
            return $this->executeMysqliStatement($sql, $params);
        }
    }
    
    // 获取WHERE条件的参数值
    private function getWhereParams() {
        $params = [];
        foreach ($this->where as $condition) {
            $params[] = $condition['value'];
        }
        return $params;
    }
    
    // 构建SELECT查询SQL
    private function buildSelectQuery() {
        $sql = "SELECT {$this->select} FROM {$this->table}";
        
        // 添加JOIN子句
        if (!empty($this->joins)) {
            foreach ($this->joins as $join) {
                $sql .= " {$join['type']} JOIN {$join['table']} ON {$join['first']} {$join['operator']} {$join['second']}";
            }
        }
        
        // 添加WHERE子句
        if (!empty($this->where)) {
            $sql .= " WHERE ";
            $whereClauses = [];
            
            foreach ($this->where as $condition) {
                $whereClauses[] = "{$condition['column']} {$condition['operator']} ?";
            }
            
            $sql .= implode(' AND ', $whereClauses);
        }
        
        // 添加GROUP BY子句
        if (!empty($this->group)) {
            $sql .= " GROUP BY " . implode(', ', $this->group);
        }
        
        // 添加ORDER BY子句
        if (!empty($this->order)) {
            $sql .= " ORDER BY " . implode(', ', $this->order);
        }
        
        // 添加LIMIT子句
        if (isset($this->limit)) {
            $sql .= " LIMIT {$this->limit}";
            
            // 添加OFFSET子句
            if (isset($this->offset)) {
                $sql .= " OFFSET {$this->offset}";
            }
        }
        
        return $sql;
    }
    
    // 构建INSERT查询SQL
    private function buildInsertQuery() {
        $columns = implode(', ', array_keys($this->values));
        $placeholders = array_fill(0, count($this->values), '?');
        $params = array_values($this->values);
        
        $placeholdersStr = implode(', ', $placeholders);
        $sql = "INSERT INTO {$this->table} ({$columns}) VALUES ({$placeholdersStr})";
        
        return [$sql, $params];
    }
    
    // 构建UPDATE查询SQL
    private function buildUpdateQuery() {
        $setClauses = [];
        $params = [];
        
        foreach ($this->updateData as $column => $value) {
            $setClauses[] = "{$column} = ?";
            $params[] = $value;
        }
        
        $sql = "UPDATE {$this->table} SET " . implode(', ', $setClauses);
        
        // 添加WHERE子句
        if (!empty($this->where)) {
            $sql .= " WHERE ";
            $whereClauses = [];
            
            foreach ($this->where as $condition) {
                $whereClauses[] = "{$condition['column']} {$condition['operator']} ?";
                $params[] = $condition['value'];
            }
            
            $sql .= implode(' AND ', $whereClauses);
        }
        
        return [$sql, $params];
    }
    
    // 构建DELETE查询SQL
    private function buildDeleteQuery() {
        $sql = "DELETE FROM {$this->table}";
        $params = [];
        
        // 添加WHERE子句
        if (!empty($this->where)) {
            $sql .= " WHERE ";
            $whereClauses = [];
            
            foreach ($this->where as $condition) {
                $whereClauses[] = "{$condition['column']} {$condition['operator']} ?";
                $params[] = $condition['value'];
            }
            
            $sql .= implode(' AND ', $whereClauses);
        }
        
        return [$sql, $params];
    }
    
    // 使用PDO执行查询 - 带参数
    private function executePdoQuery($sql, $params = []) {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (PDOException $e) {
            throw new Exception("PDO查询错误: " . $e->getMessage());
        }
    }
    
    // 使用PDO执行语句 - 带参数
    private function executePdoStatement($sql, $params = []) {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->rowCount();
        } catch (PDOException $e) {
            throw new Exception("PDO语句错误: " . $e->getMessage());
        }
    }
    
    // 使用MySQLi执行查询 - 带参数
    private function executeMysqliQuery($sql, $params = []) {
        try {
            $stmt = $this->mysqli->prepare($sql);
            
            if ($stmt === false) {
                throw new Exception("SQL准备失败: " . $this->mysqli->error);
            }
            
            if (!empty($params)) {
                $types = '';
                $bindParams = [];
                
                foreach ($params as $param) {
                    if (is_int($param)) {
                        $types .= 'i';
                    } elseif (is_float($param)) {
                        $types .= 'd';
                    } elseif (is_string($param)) {
                        $types .= 's';
                    } else {
                        $types .= 'b'; // 布尔值或其他类型
                    }
                }
                
                $bindParams[] = &$types;
                
                foreach ($params as &$param) {
                    $bindParams[] = &$param;
                }
                
                // 使用反射API绑定参数
                $reflection = new ReflectionClass('mysqli_stmt');
                $method = $reflection->getMethod('bind_param');
                $method->invokeArgs($stmt, $bindParams);
            }
            
            $stmt->execute();
            $result = $stmt->get_result();
            $rows = $result->fetch_all(MYSQLI_ASSOC);
            $stmt->close();
            
            return $rows;
        } catch (Exception $e) {
            throw new Exception("MySQLi查询错误: " . $e->getMessage());
        }
    }
    
    // 使用MySQLi执行语句 - 带参数
    private function executeMysqliStatement($sql, $params = []) {
        try {
            $stmt = $this->mysqli->prepare($sql);
            
            if ($stmt === false) {
                throw new Exception("SQL准备失败: " . $this->mysqli->error);
            }
            
            if (!empty($params)) {
                $types = '';
                $bindParams = [];
                
                foreach ($params as $param) {
                    if (is_int($param)) {
                        $types .= 'i';
                    } elseif (is_float($param)) {
                        $types .= 'd';
                    } elseif (is_string($param)) {
                        $types .= 's';
                    } else {
                        $types .= 'b'; // 布尔值或其他类型
                    }
                }
                
                $bindParams[] = &$types;
                
                foreach ($params as &$param) {
                    $bindParams[] = &$param;
                }
                
                // 使用反射API绑定参数
                $reflection = new ReflectionClass('mysqli_stmt');
                $method = $reflection->getMethod('bind_param');
                $method->invokeArgs($stmt, $bindParams);
            }
            
            $stmt->execute();
            $affectedRows = $stmt->affected_rows;
            $stmt->close();
            
            return $affectedRows;
        } catch (Exception $e) {
            throw new Exception("MySQLi语句错误: " . $e->getMessage());
        }
    }
    
    // 开始事务
    public function beginTransaction() {
        if ($this->pdo) {
            $this->pdo->beginTransaction();
        } else {
            $this->mysqli->begin_transaction();
        }
        return $this;
    }
    
    // 提交事务
    public function commit() {
        if ($this->pdo) {
            $this->pdo->commit();
        } else {
            $this->mysqli->commit();
        }
        return $this;
    }
    
    // 回滚事务
    public function rollback() {
        if ($this->pdo) {
            $this->pdo->rollBack();
        } else {
            $this->mysqli->rollback();
        }
        return $this;
    }
    
    // 重置查询构建器
    public function reset() {
        $this->select = '*';
        $this->where = [];
        $this->joins = [];
        $this->limit = null;
        $this->offset = null;
        $this->order = [];
        $this->group = [];
        $this->values = [];
        $this->updateData = [];
        $this->params = [];
        return $this;
    }
    
}
